import pandas as pd
import re
from  pathlib import Path

class ConstructionDataProcessor:
    def __init__(self, debug=False):
        """
        初始化处理器（支持可选传入涵洞设置一览表）

        :param excel_path: 施工混凝土浇筑台账路径
        :param all_piles_path: 所有桩号坐标文件路径
        :param culvert_info_path: 涵洞设置一览表路径（可选）
        :param debug: 是否开启调试模式
        """
        #获取当前文件地址
        self.excel_path = Path(__file__).parent / "施工混凝土浇筑台账.xlsx"
        self.all_piles_path = Path(__file__).parent / "所有的桩号.xlsx"
        self.culvert_info_path = Path(__file__).parent / "涵洞设置一览表.xlsx"
        self.debug = debug

        # 内部数据
        self.df_raw = None
        self.df_combined = None
        self.df_culvert = None
        self.df_result = None
        self.df_result_with_coords = None
        self.df_final = None

        # 初始化加载数据
        self._load_raw_data()
        self._load_all_piles()

        if self.culvert_info_path:
            self._load_culvert_info()

    def _log(self, message):
        """调试日志输出"""
        if self.debug:
            print(f"[DEBUG] {message}")

    def _load_raw_data(self):
        """读取施工混凝土浇筑台账数据"""
        self._log("加载施工台账数据...")
        df = pd.read_excel(self.excel_path)

        # 提取字段
        df['轴线'] = df['工程名称'].str.extract(r'(K\d+\+\d+(?:\.\d+)?)', expand=False)
        df['左右幅'] = df['工程名称'].apply(lambda x: '左幅' if '左幅' in x else ('右幅' if '右幅' in x else ''))
        df['类型'] = df['工程名称'].str.extract(r'(圆管涵|盖板涵|箱涵|通道)', expand=False)

        # 清洗部位字段
        df['匹配部位'] = df['匹配部位'].apply(lambda x: re.sub(r'[左右]幅', '', x).strip())

        # 保留需要的列
        self.df_raw = df[['轴线', '类型', '施工部位', '匹配部位', '左右幅', '施工日期', '强度等级', '数量m³']].copy()
        self._log("施工台账数据加载完成")

    def _load_all_piles(self):
        """读取所有桩号坐标数据"""
        self._log("加载桩号坐标数据...")
        df2 = pd.read_excel(self.all_piles_path)

        # 提取所有“桩号 + 坐标”的组合块
        columns = df2.columns.tolist()
        groups = [columns[i:i + 3] for i in range(0, len(columns), 3)]

        result_dict = {"桩号": [], "坐标N(X)": [], "坐标E(Y)": []}

        for group in groups:
            col_pile, col_x, col_y = group
            valid_mask = df2[col_pile].notna()
            result_dict["桩号"].extend(df2.loc[valid_mask, col_pile].values)
            result_dict["坐标N(X)"].extend(df2.loc[valid_mask, col_x].values)
            result_dict["坐标E(Y)"].extend(df2.loc[valid_mask, col_y].values)

        self.df_combined = pd.DataFrame(result_dict)
        self._log("桩号坐标数据加载完成")

    def _load_culvert_info(self):
        """读取涵洞设置一览表"""
        self._log("加载涵洞设置信息...")
        self.df_culvert = pd.read_excel(self.culvert_info_path)
        self.df_culvert['中心桩号'] = self.df_culvert['中心桩号'].astype(str).apply(self._clean_pile)
        self._log("涵洞设置信息加载完成")

    def _clean_pile(self, pile):
        if isinstance(pile, str):
            return pile.strip().replace('.0', '')
        return ''

    def _create_pile_matcher(self):
        """创建桩号坐标匹配器"""

        class PileCoordinateMatcher:
            def __init__(self, df_combined):
                self.df_combined = df_combined.copy()
                self._preprocess()

            def _is_valid_pile(self, pile):
                return isinstance(pile, str) and re.match(r'^K\d+\+\d+(?:\.\d+)?$', pile)

            def _extract_km(self, pile):
                parts = pile.split('+')
                km_part = int(parts[0][1:]) * 1000
                meter_part = float(parts[1])
                return km_part + meter_part

            def _preprocess(self):
                self.df_combined['桩号数值'] = self.df_combined['桩号'].apply(self._extract_km)

            def get_closest_coords(self, target_pile):
                if not self._is_valid_pile(target_pile):
                    return [pd.NA, pd.NA]
                try:
                    target_km = self._extract_km(target_pile)
                    distances = abs(self.df_combined['桩号数值'] - target_km)
                    nearest_row = self.df_combined.iloc[distances.idxmin()]
                    return [nearest_row['坐标N(X)'], nearest_row['坐标E(Y)']]
                except Exception as e:
                    print(f"[警告] 坐标匹配失败: {e}")
                    return [pd.NA, pd.NA]

            def match_df(self, df_input, pile_col='轴线'):
                results = []
                for index, row in df_input.iterrows():
                    pile = row[pile_col]
                    coords = self.get_closest_coords(pile)
                    try:
                        nearest_row = self.df_combined.iloc[
                            abs(self.df_combined['桩号数值'] - self._extract_km(pile)).idxmin()
                        ]
                        nearest_pile = nearest_row['桩号']
                    except:
                        nearest_pile = pd.NA

                    results.append({
                        '原始桩号': pile,
                        '最近桩号': nearest_pile,
                        '坐标N(X)': coords[0],
                        '坐标E(Y)': coords[1],
                        '两者距离(米)': abs(self._extract_km(nearest_pile) - self._extract_km(pile)) if isinstance(coords[0], (int, float)) else pd.NA
                    })

                df_results = pd.DataFrame(results)
                return pd.concat([
                    df_input.reset_index(drop=True),
                    df_results.reset_index(drop=True)
                ], axis=1)

        return PileCoordinateMatcher(self.df_combined)

    def process(self):
        """执行完整处理流程"""
        self._log("开始处理施工部位数据...")
        self._log("步骤1: 提取施工部位信息")
        self.df_result = self.df_raw.copy()

        self._log("步骤2: 匹配坐标")
        matcher = self._create_pile_matcher()
        self.df_result_with_coords = matcher.match_df(self.df_result, pile_col='轴线')

        # 如果提供了涵洞设置信息，并且成功加载了 df_culvert，则进行合并
        if hasattr(self, 'df_culvert') and self.df_culvert is not None:
            self._log("步骤3: 合并涵洞设置信息")

            # 清洗字段
            self.df_result_with_coords['轴线'] = self.df_result_with_coords['轴线'].astype(str).apply(self._clean_pile)
            self.df_culvert['中心桩号'] = self.df_culvert['中心桩号'].astype(str).apply(self._clean_pile)

            # 构建匹配键
            self.df_result_with_coords['桩号_类型'] = self.df_result_with_coords['轴线'] + '_' + self.df_result_with_coords['类型']
            self.df_culvert['桩号_类型'] = self.df_culvert['中心桩号'] + '_' + self.df_culvert['结构类型']

            # 使用 merge 合并数据
            self.df_final = self.df_result_with_coords.merge(
                self.df_culvert[['桩号_类型', '涵底中心标高', '实际涵长']],
                on='桩号_类型',
                how='left'
            )

            # 删除临时列
            self.df_final.drop(columns=['桩号_类型'], inplace=True)

        else:
            self.df_final = self.df_result_with_coords

        self._log("处理完成！")
        return self.df_final

    def get_final_dataframe(self):
        """返回最终结果 DataFrame"""
        if self.df_final is None:
            raise ValueError("尚未调用 process() 进行处理")
        return self.df_final


if __name__ == "__main__":
    # 创建实例（debug=True 开启调试输出）
    # processor = ConstructionDataProcessor(
    #     excel_path=r'C:\Users\Administrator\Desktop\工作常用程序\公路资料\检验批\frame\施工混凝土浇筑台账.xlsx',
    #     all_piles_path=r'C:\Users\Administrator\Desktop\工作常用程序\公路资料\检验批\frame\所有的桩号.xlsx',
    #     culvert_info_path=r'C:\Users\Administrator\Desktop\工作常用程序\公路资料\检验批\frame\涵洞设置一览表.xlsx',
    #     debug=True
    # )

    # # 执行处理流程
    # final_df = processor.process()

    # # 输出结果
    # print(final_df.head())
    print(Path(__file__).parent / "混凝土施工总台帐.xlsx")